﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data;
using System.Windows.Forms;
using System.Data.Linq.SqlClient;


namespace UniMana.Data
{
    public static class Training_StudentProfileData
    {

        /// <summary>
        /// Lấy danh sách hồ sơ thiếu theo mã sinh viên
        /// </summary>
        /// <param name="nCheckList"></param>
        /// <param name="nStudent"></param>
        /// <param name="nStudentClass"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static DataTable GetListDebtProfileByID(this Table<Training_Student_Checklist> nCheckList, Table<Training_Student> nStudent,
                                             Table<Training_Student_Class> nStudentClass,
                                             string ID)
        {
            #region [Lấy danh sách sinh viên còn nợ hồ sơ]
            var studentList = from ckl in nCheckList
                              join std in nStudent on ckl.Training_StudentKey equals std.Training_StudentKey
                              join std_cls in nStudentClass on ckl.Training_StudentKey equals std_cls.Training_StudentKey
                              where (ckl.CertificateAdmitted == null || ckl.CertificateAdmitted == false)
                                    || (ckl.CertificateBirth == null || ckl.CertificateBirth == false)
                                    || (ckl.CertificateGrad == null || ckl.CertificateGrad == false)
                                    || (ckl.CertificateHealthy == null || ckl.CertificateHealthy == false)
                                    || (ckl.CertificateIdentify == null || ckl.CertificateIdentify == false)
                                    || (ckl.CV == null || ckl.CV == false)
                                    || (ckl.LearningDiary == null || ckl.LearningDiary == false)
                              orderby std_cls.ID
                              where std_cls.ID.Contains(ID)
                              select new
                              {
                                  ckl.Training_StudentKey,
                                  ckl.CertificateAdmitted,
                                  ckl.CertificateBirth,
                                  ckl.CertificateGrad,
                                  ckl.CertificateHealthy,
                                  ckl.CertificateIdentify,
                                  ckl.CV,
                                  ckl.LearningDiary,
                                  std_cls.Catalogue_LevelKey,
                                  std_cls.Catalogue_TrainingTypeKey,
                                  std_cls.Catalogue_CourseKey,
                                  std_cls.Catalogue_MajorKey,
                                  std_cls.Catalogue_StatusKey,
                                  std_cls.Training_ClassKey,
                                  std_cls.ID,
                                  std.LastName,
                                  std.SureName,
                                  std.Birthday,
                                  std.ExceptionBirthday,
                                  Gender = std.Gender == true ? "Nữ" : "Nam",
                                  std.PlaceOfBirth,
                                  std.CellPhone,
                                  std.HomePhone,
                                  FullName = " " + std.LastName + " " + std.SureName

                              };
            #endregion


            DataColumn cl1 = new DataColumn("Training_StudentKey");
            DataColumn cl2 = new DataColumn("ID");
            DataColumn cl3 = new DataColumn("LastName");
            DataColumn cl4 = new DataColumn("SureName");
            DataColumn cl5 = new DataColumn("Gender");
            DataColumn cl6 = new DataColumn("Birthday");
            DataColumn cl7 = new DataColumn("Debt");
            DataColumn cl8 = new DataColumn("Catalogue_LevelKey");
            DataColumn cl9 = new DataColumn("Catalogue_TrainingTypeKey");
            DataColumn cl10 = new DataColumn("Catalogue_CourseKey");
            DataColumn cl11 = new DataColumn("Catalogue_StatusKey");
            DataColumn cl12 = new DataColumn("Training_ClassKey");
            DataColumn cl13 = new DataColumn("ExceptionBirthday");
            DataColumn cl14 = new DataColumn("PlaceOfBirth");
            DataColumn cl15 = new DataColumn("CellPhone");
            DataColumn cl16 = new DataColumn("HomePhone");
            DataColumn cl17 = new DataColumn("Catalogue_MajorKey");

            DataTable tbl = new DataTable();
            tbl.Columns.AddRange(new DataColumn[] { cl1, cl2, cl3, cl4, cl5, cl6, cl7, cl8, cl9, cl10, cl11, cl12, cl13, cl14, cl15, cl16,cl17 });

            foreach (var item in studentList)
            {
                DataRow r = tbl.NewRow();
                r["Training_StudentKey"] = item.Training_StudentKey;
                r["ID"] = item.ID;
                r["LastName"] = item.LastName;
                r["SureName"] = item.SureName;
                r["Gender"] = item.Gender;
                r["Birthday"] = item.Birthday;
                
                r["Catalogue_LevelKey"] = item.Catalogue_LevelKey;
                r["Catalogue_TrainingTypeKey"] = item.Catalogue_TrainingTypeKey;
                r["Catalogue_CourseKey"] = item.Catalogue_CourseKey;
                r["Catalogue_StatusKey"] = item.Catalogue_StatusKey;
                r["Training_ClassKey"] = item.Training_ClassKey;
                r["ExceptionBirthday"] = item.ExceptionBirthday;
                r["PlaceOfBirth"] = item.PlaceOfBirth;
                r["CellPhone"] = item.CellPhone;
                r["HomePhone"] = item.HomePhone;
                r["Catalogue_MajorKey"] = item.Catalogue_MajorKey;


                string debt = string.Empty;

                #region [Liệt kê hồ sơ thiếu]
                if (item.CertificateAdmitted == null || item.CertificateAdmitted == false)
                {
                    debt += "Giấy báo nhập học";
                }
                if (item.CertificateHealthy == null || item.CertificateHealthy == false)
                {
                    debt += ", Giấy chứng nhận sức khỏe";
                }
                if (item.CertificateBirth == null || item.CertificateBirth == false)
                {
                    debt += ", Giấy khai sinh";
                }
                if (item.CV == null || item.CV == false)
                {
                    debt += ", Sơ yếu lí lịch";
                }
                if (item.CertificateGrad == null || item.CertificateGrad == false)
                {
                    debt += ", Giấy chứng nhận tốt nghiệp/Bản sao bằng tốt nghiệp";
                }
                if (item.LearningDiary == null || item.LearningDiary == false)
                {
                    debt += ", Học bạ";
                }
                if (item.CertificateIdentify == null || item.CertificateIdentify == false)
                {
                    debt += ", Bản sao CMND";
                }
                #endregion

                if (debt[0] == ',') debt = debt.Substring(1, debt.Length - 1);

                r["Debt"] = debt;

                tbl.Rows.Add(r);
            }
            return tbl;
        }


        /// <summary>
        /// Tìm kiếm sinh viên theo khóa
        /// </summary>
        /// <param name="nCheckList"></param>
        /// <param name="nStudent"></param>
        /// <param name="nStudentClass"></param>
        /// <param name="_strName"></param>
        /// <returns></returns>
        public static DataTable GetListDebtProfileByName(this Table<Training_Student_Checklist> nCheckList, Table<Training_Student> nStudent,
                                     Table<Training_Student_Class> nStudentClass,
                                     string _strName)
        {
            #region [Lấy danh sách sinh viên còn nợ hồ sơ]
            var studentList = from ckl in nCheckList
                              join std in nStudent on ckl.Training_StudentKey equals std.Training_StudentKey
                              join std_cls in nStudentClass on ckl.Training_StudentKey equals std_cls.Training_StudentKey
                              where (ckl.CertificateAdmitted == null || ckl.CertificateAdmitted == false)
                                    || (ckl.CertificateBirth == null || ckl.CertificateBirth == false)
                                    || (ckl.CertificateGrad == null || ckl.CertificateGrad == false)
                                    || (ckl.CertificateHealthy == null || ckl.CertificateHealthy == false)
                                    || (ckl.CertificateIdentify == null || ckl.CertificateIdentify == false)
                                    || (ckl.CV == null || ckl.CV == false)
                                    || (ckl.LearningDiary == null || ckl.LearningDiary == false)
                              orderby std_cls.ID
                              select new
                              {
                                  ckl.Training_StudentKey,
                                  ckl.CertificateAdmitted,
                                  ckl.CertificateBirth,
                                  ckl.CertificateGrad,
                                  ckl.CertificateHealthy,
                                  ckl.CertificateIdentify,
                                  ckl.CV,
                                  ckl.LearningDiary,
                                  std_cls.Catalogue_LevelKey,
                                  std_cls.Catalogue_TrainingTypeKey,
                                  std_cls.Catalogue_CourseKey,
                                  std_cls.Catalogue_MajorKey,
                                  std_cls.Catalogue_StatusKey,
                                  std_cls.Training_ClassKey,
                                  std_cls.ID,
                                  std.LastName,
                                  std.SureName,
                                  std.Birthday,
                                  std.ExceptionBirthday,
                                  Gender = std.Gender == true ? "Nữ" : "Nam",
                                  std.PlaceOfBirth,
                                  std.CellPhone,
                                  std.HomePhone,
                                  FullName = " "+std.LastName +" "+std.SureName

                              };
            studentList = studentList.Where(p => p.FullName.EndsWith(" " + _strName));
            #endregion


            DataColumn cl1 = new DataColumn("Training_StudentKey");
            DataColumn cl2 = new DataColumn("ID");
            DataColumn cl3 = new DataColumn("LastName");
            DataColumn cl4 = new DataColumn("SureName");
            DataColumn cl5 = new DataColumn("Gender");
            DataColumn cl6 = new DataColumn("Birthday");
            DataColumn cl7 = new DataColumn("Debt");
            DataColumn cl8 = new DataColumn("Catalogue_LevelKey");
            DataColumn cl9 = new DataColumn("Catalogue_TrainingTypeKey");
            DataColumn cl10 = new DataColumn("Catalogue_CourseKey");
            DataColumn cl11 = new DataColumn("Catalogue_StatusKey");
            DataColumn cl12 = new DataColumn("Training_ClassKey");
            DataColumn cl13 = new DataColumn("ExceptionBirthday");
            DataColumn cl14 = new DataColumn("PlaceOfBirth");
            DataColumn cl15 = new DataColumn("CellPhone");
            DataColumn cl16 = new DataColumn("HomePhone");
            DataColumn cl17 = new DataColumn("Catalogue_MajorKey");

            DataTable tbl = new DataTable();
            tbl.Columns.AddRange(new DataColumn[] { cl1, cl2, cl3, cl4, cl5, cl6, cl7, cl8, cl9, cl10, cl11, cl12, cl13, cl14, cl15, cl16, cl17 });

            foreach (var item in studentList)
            {
                DataRow r = tbl.NewRow();
                r["Training_StudentKey"] = item.Training_StudentKey;
                r["ID"] = item.ID;
                r["LastName"] = item.LastName;
                r["SureName"] = item.SureName;
                r["Gender"] = item.Gender;
                r["Birthday"] = item.Birthday;

                r["Catalogue_LevelKey"] = item.Catalogue_LevelKey;
                r["Catalogue_TrainingTypeKey"] = item.Catalogue_TrainingTypeKey;
                r["Catalogue_CourseKey"] = item.Catalogue_CourseKey;
                r["Catalogue_StatusKey"] = item.Catalogue_StatusKey;
                r["Training_ClassKey"] = item.Training_ClassKey;
                r["ExceptionBirthday"] = item.ExceptionBirthday;
                r["PlaceOfBirth"] = item.PlaceOfBirth;
                r["CellPhone"] = item.CellPhone;
                r["HomePhone"] = item.HomePhone;
                r["Catalogue_MajorKey"] = item.Catalogue_MajorKey;


                string debt = string.Empty;

                #region [Liệt kê hồ sơ thiếu]
                if (item.CertificateAdmitted == null || item.CertificateAdmitted == false)
                {
                    debt += "Giấy báo nhập học";
                }
                if (item.CertificateHealthy == null || item.CertificateHealthy == false)
                {
                    debt += ", Giấy chứng nhận sức khỏe";
                }
                if (item.CertificateBirth == null || item.CertificateBirth == false)
                {
                    debt += ", Giấy khai sinh";
                }
                if (item.CV == null || item.CV == false)
                {
                    debt += ", Sơ yếu lí lịch";
                }
                if (item.CertificateGrad == null || item.CertificateGrad == false)
                {
                    debt += ", Giấy chứng nhận tốt nghiệp/Bản sao bằng tốt nghiệp";
                }
                if (item.LearningDiary == null || item.LearningDiary == false)
                {
                    debt += ", Học bạ";
                }
                if (item.CertificateIdentify == null || item.CertificateIdentify == false)
                {
                    debt += ", Bản sao CMND";
                }
                #endregion

                if (debt[0] == ',') debt = debt.Substring(1, debt.Length - 1);

                r["Debt"] = debt;

                tbl.Rows.Add(r);
            }
            return tbl;
        }

        /// <summary>
        /// Lấy danh sách hồ sơ thiếu theo các thông số được cung cấp
        /// </summary>
        /// <param name="nCheckList"></param>
        /// <param name="nStudent"></param>
        /// <param name="nStudentClass"></param>
        /// <param name="_iLevelKey"></param>
        /// <param name="_iTrainingTypeKey"></param>
        /// <param name="_iCourseKey"></param>
        /// <param name="_iDeparmentKey"></param>
        /// <param name="_iMajorKey"></param>
        /// <returns></returns>
        public static DataTable GetListDebtProfile(this Table<Training_Student_Checklist> nCheckList, Table<Training_Student> nStudent,
                                                     Table<Training_Student_Class> nStudentClass,
                                                     int _iLevelKey = -1, int _iTrainingTypeKey = -1, int _iCourseKey = -1, 
                                                     int _iDeparmentKey = -1, int _iMajorKey = -1, int _iClassKey=-1)
        {
            #region [Lấy danh sách sinh viên còn nợ hồ sơ]
            var studentList = from ckl in nCheckList
                     join std in nStudent on ckl.Training_StudentKey equals std.Training_StudentKey
                     join std_cls in nStudentClass on ckl.Training_StudentKey equals std_cls.Training_StudentKey
                     where (ckl.CertificateAdmitted == null || ckl.CertificateAdmitted == false)
                           || (ckl.CertificateBirth == null || ckl.CertificateBirth == false)
                           || (ckl.CertificateGrad == null || ckl.CertificateGrad == false)
                           || (ckl.CertificateHealthy == null || ckl.CertificateHealthy == false)
                           || (ckl.CertificateIdentify == null || ckl.CertificateIdentify == false)
                           || (ckl.CV == null || ckl.CV == false)
                           || (ckl.LearningDiary == null || ckl.LearningDiary == false)
                     orderby std_cls.ID
                     select new
                     {
                         ckl.Training_StudentKey,
                         ckl.CertificateAdmitted,
                         ckl.CertificateBirth,
                         ckl.CertificateGrad,
                         ckl.CertificateHealthy,
                         ckl.CertificateIdentify,
                         ckl.CV,
                         ckl.LearningDiary,
                         std_cls.Catalogue_LevelKey,
                         std_cls.Catalogue_TrainingTypeKey,
                         std_cls.Catalogue_CourseKey,
                         std_cls.Catalogue_MajorKey,
                         std_cls.Catalogue_StatusKey,
                         std_cls.Training_ClassKey,
                         std_cls.ID,
                         std.LastName,
                         std.SureName,
                         std.Birthday,
                         std.ExceptionBirthday,
                         Gender = std.Gender == true ? "Nữ" : "Nam",
                         std.PlaceOfBirth,
                         std.CellPhone,
                         std.HomePhone,
                         FullName = " " + std.LastName + " " + std.SureName
                         
                     };
            //studentList = studentList.OrderBy(p => p.ID);
            #endregion

            #region [Điều kiện lọc]
            if (_iLevelKey != -1) { studentList = studentList.Where(p => p.Catalogue_LevelKey == _iLevelKey); }
            if (_iClassKey != -1) { studentList = studentList.Where(p => p.Training_ClassKey == _iClassKey); }
            if (_iTrainingTypeKey != -1) { studentList = studentList.Where(p => p.Catalogue_TrainingTypeKey == _iTrainingTypeKey); }
            if (_iCourseKey != -1) { studentList = studentList.Where(p => p.Catalogue_CourseKey == _iCourseKey); }
            if (_iMajorKey != -1) { studentList = studentList.Where(p => p.Catalogue_MajorKey == _iMajorKey); }

            if (_iDeparmentKey != -1 && _iMajorKey == -1)
            {
                DBMain m_data = new DBMain();
                var majorList = from maj in m_data.m_DCReadOnly.Catalogue_Majors
                                where maj.Catalogue_DepartmentKey == _iDeparmentKey
                                select new { maj.Catalogue_MajorKey };

                List<int> majList = new List<int>();

                foreach (var item in majorList)
                {
                    majList.Add(item.Catalogue_MajorKey);
                }

                studentList = from list in studentList
                              where majList.Contains(list.Catalogue_MajorKey)
                              select list;
            }
            #endregion


            DataColumn cl1 = new DataColumn("Training_StudentKey");
            DataColumn cl2 = new DataColumn("ID");
            DataColumn cl3 = new DataColumn("LastName");
            DataColumn cl4 = new DataColumn("SureName");
            DataColumn cl5 = new DataColumn("Gender");
            DataColumn cl6 = new DataColumn("Birthday");
            DataColumn cl7 = new DataColumn("Debt");
            DataColumn cl8 = new DataColumn("Catalogue_LevelKey");
            DataColumn cl9 = new DataColumn("Catalogue_TrainingTypeKey");
            DataColumn cl10 = new DataColumn("Catalogue_CourseKey");
            DataColumn cl11 = new DataColumn("Catalogue_StatusKey");
            DataColumn cl12 = new DataColumn("Training_ClassKey");
            DataColumn cl13 = new DataColumn("ExceptionBirthday");
            DataColumn cl14 = new DataColumn("PlaceOfBirth");
            DataColumn cl15 = new DataColumn("CellPhone");
            DataColumn cl16 = new DataColumn("HomePhone");
            DataColumn cl17 = new DataColumn("Catalogue_MajorKey");

            DataTable tbl = new DataTable();
            tbl.Columns.AddRange(new DataColumn[] { cl1, cl2, cl3, cl4, cl5, cl6, cl7, cl8, cl9, cl10, cl11, cl12, cl13, cl14, cl15, cl16, cl17 });

            foreach (var item in studentList)
            {
                DataRow r = tbl.NewRow();
                r["Training_StudentKey"] = item.Training_StudentKey;
                r["ID"] = item.ID;
                r["LastName"] = item.LastName;
                r["SureName"] = item.SureName;
                r["Gender"] = item.Gender;
                r["Birthday"] = item.Birthday;

                r["Catalogue_LevelKey"] = item.Catalogue_LevelKey;
                r["Catalogue_TrainingTypeKey"] = item.Catalogue_TrainingTypeKey;
                r["Catalogue_CourseKey"] = item.Catalogue_CourseKey;
                r["Catalogue_StatusKey"] = item.Catalogue_StatusKey;
                r["Training_ClassKey"] = item.Training_ClassKey;
                r["ExceptionBirthday"] = item.ExceptionBirthday;
                r["PlaceOfBirth"] = item.PlaceOfBirth;
                r["CellPhone"] = item.CellPhone;
                r["HomePhone"] = item.HomePhone;
                r["Catalogue_MajorKey"] = item.Catalogue_MajorKey;


                string debt = string.Empty;

                #region [Liệt kê hồ sơ thiếu]
                if (item.CertificateAdmitted == null || item.CertificateAdmitted == false)
                {
                    debt += "Giấy báo nhập học";
                }
                if (item.CertificateHealthy == null || item.CertificateHealthy == false)
                {
                    debt += ", Giấy chứng nhận sức khỏe";
                }
                if (item.CertificateBirth == null || item.CertificateBirth == false)
                {
                    debt += ", Giấy khai sinh";
                }
                if (item.CV == null || item.CV == false)
                {
                    debt += ", Sơ yếu lí lịch";
                }
                if (item.CertificateGrad == null || item.CertificateGrad == false)
                {
                    debt += ", Giấy chứng nhận tốt nghiệp/Bản sao bằng tốt nghiệp";
                }
                if (item.LearningDiary == null || item.LearningDiary == false)
                {
                    debt += ", Học bạ";
                }
                if (item.CertificateIdentify == null || item.CertificateIdentify == false)
                {
                    debt += ", Bản sao CMND";
                }
                #endregion

                if (debt[0] == ',') debt = debt.Substring(1, debt.Length-1);

                r["Debt"] = debt;

                tbl.Rows.Add(r);
            }
            return tbl;
        }

    }
}
